﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using BSCMFF.Core.BusinessLayer;
using System.IO;
using BSCMFF.Core.DataAccessLayer;
using System.Text.RegularExpressions;
using Telerik.Web.UI;
using BSCMFF.Core;
using System.Drawing;

namespace BSCMFF.Web.Pages.Administration
{
    /// <summary>
    /// Imports users from csv file.
    /// </summary>
    /// <remarks></remarks>
    public partial class UserImport : MultiLanguagePage
    {
        /// <summary>
        /// Sets language for radGrids.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        /// <remarks></remarks>
        protected void Page_Load(object sender, EventArgs e)
        {
            RadGridErrors.Culture = System.Threading.Thread.CurrentThread.CurrentCulture;
            RadGridPreview.Culture = System.Threading.Thread.CurrentThread.CurrentCulture;
            //ImportInstructions();
        }

        /// <summary>
        /// Imports the instructions from the file and formats it for user.
        /// </summary>
        /// <remarks></remarks>
        private void ImportInstructions()
        {
            string fileName = Server.MapPath(@"~/Resources/import instructions.txt");

            StreamReader sr = File.OpenText(fileName);

            string instructions = sr.ReadToEnd();


            //LabelInstructions.Text = "<table><tr><td>";


            string insctructionsreplaced = instructions.Replace(Environment.NewLine, "</td></tr><tr><td>");

            string insctructionsreplaced2 = insctructionsreplaced.Replace("\t", "</td><td>");

            LabelInstructions.Text = "<table><tr><td>" + insctructionsreplaced2 + "</table>"; 
        }


        /// <summary>
        /// Show user records from csv file for checking out.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        /// <remarks></remarks>
        protected void ButtonShowPreview_Click(object sender, EventArgs e)
        {
            PanelSuccess.Visible = false;
            PanelErrors.Visible = false;

            if (System.Web.HttpContext.Current.Cache["pokus"] != null)
            {
                System.Web.HttpContext.Current.Cache.Remove("pokus");
            }

            List<string[]> parsedData = new List<string[]>();

            //there must be csv file with users chosen.
            if (RadUploadCSV.UploadedFiles.Count == 0)
            {
                return;
            }

            LiteralPreview.Text = "Preview - \"" + RadUploadCSV.UploadedFiles[0].FileName + "\"";
            CSVReader reader = new CSVReader(RadUploadCSV.UploadedFiles[0].InputStream);

            //get the header
            string[] headers = reader.GetCSVLine();

            DataTable dtOk = new DataTable();
            DataTable dtError = new DataTable();
            DataTable dtPreview = new DataTable();


            //add headers
            foreach (string strHeader in headers)
            {
                dtOk.Columns.Add(strHeader);
                dtError.Columns.Add(strHeader);
                dtPreview.Columns.Add(strHeader);
            }


            string[] data;
            int previewCount = 0;

            while ((data = reader.GetCSVLine()) != null)
            {
                if (true)
                {
                    dtOk.Rows.Add(data);
                    if (previewCount++ < 10)
                    {
                        dtPreview.Rows.Add(data);
                    }

                }
                //else
                //{
                //    dtError.Rows.Add(data);
                //}
            }
            
            //insert data table with records into cache (for import)
            System.Web.HttpContext.Current.Cache.Add("pokus", dtOk, null, DateTime.Now.AddMinutes(10), System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, null);
            

            RadGridPreview.DataSource = dtPreview;
            RadGridPreview.DataBind();

            PanelPreview.Visible = true;
        }

        /// <summary>
        /// Imports users from file (cached) into database.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        /// <remarks></remarks>
        protected void RadButtonConfirmUpload_Click(object sender, EventArgs e)
        {
            //takes data from cache
            DataTable dt =  (DataTable)System.Web.HttpContext.Current.Cache["pokus"];

            //if it has expired..
            if (dt == null)
            {
                throw new Exception("cache expired");
            }

            System.Web.HttpContext.Current.Cache.Remove("pokus"); 

            UploadDataIntoSql(dt);
        }

        /// <summary>
        /// Uploads the data into SQL.
        /// </summary>
        /// <param name="dt">The dt.</param>
        /// <remarks></remarks>
        private void UploadDataIntoSql(DataTable dt)
        {
            DataTable dtOk;
            dtOk = CheckDataTypes(dt);
            BSCEntities context = new BSCEntities();

            
            foreach (DataRow dr in dtOk.Rows)
            {
                User u = new User();

                u.Firstname = dr.ItemArray[0].ToString();
                u.Lastname = dr.ItemArray[1].ToString();
                u.Email = dr.ItemArray[2].ToString();

                //BSCMFF.Core.RoleDataProvider.EntityRoleDataProvider rp = new Core.RoleDataProvider.EntityRoleDataProvider();
                //rp.AddUsersToRoles(

                BSCMFF.Core.BusinessLayer.RoleManager rm = new RoleManager();

                string roleName = dr.ItemArray[3].ToString();
                if (roleName == "")
                    roleName = "Employee";
                u.Role_FK = rm.GetRole_ID(roleName);

                u.Login = dr.ItemArray[4].ToString();
                u.Password = dr.ItemArray[5].ToString();
                u.EmployeeID = dr.ItemArray[6].ToString();
                

                //department
                BSCMFF.Core.BusinessLayer.DepartmentManager dm = new DepartmentManager();
                if (dr.ItemArray[7].ToString() != "")
                {
                    u.SBU_FK = dm.GetDepartmentID(dr.ItemArray[7].ToString());
                }
                //job position
                u.Job_Position = dr.ItemArray[8].ToString();
                //hiredate
                if (dr.ItemArray[9].ToString() != "")
                {
                    u.HireDate = DateTime.Parse(dr.ItemArray[9].ToString());
                }
                //salary
                if (dr.ItemArray[10].ToString() != "")
                {
                    u.Salary = decimal.Parse(dr.ItemArray[10].ToString());
                }
                //superviser
                if (dr.ItemArray[11].ToString() != "")
                {
                    UserManager um = new UserManager();
                    User su = um.GetUserByLogin(dr.ItemArray[11].ToString());
                    if (su != null)
                    {
                        u.Manager_FK = su.ID;
                    }
                    else
                    {

                    }
                }

                context.AddToUsers(u);
            }
            PanelSuccess.Visible = true;
            PanelPreview.Visible = false;
            try
            {
                context.SaveChanges();
                if (dt.Rows.Count == dtOk.Rows.Count)
                {
                    LabelSuccess.Text = "Data were uploaded successfully";
                    LabelSuccess.ForeColor = Color.Green;
                }
                else
                {
                    if (dtOk.Rows.Count > 0)
                    {
                        LabelSuccess.Text = "Some data were uploaded successfully, the others are not valid";
                        LabelSuccess.ForeColor = Color.Yellow;
                    }
                    else
                    {
                        LabelSuccess.Text = "No valid data";
                        LabelSuccess.ForeColor = Color.Red;
                    }
                }
            }
            catch (Exception ex)
            {
                LabelSuccess.Text = "There was an error in uploading data : " + ex.Message;
                LabelSuccess.ForeColor = Color.Red;
            }
        }

        /// <summary>
        /// Checks correctness of data.
        /// </summary>
        /// <param name="dt">The dt.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        private DataTable CheckDataTypes(DataTable dt)
        {
            DataTable dtOk = new DataTable();
            DataTable dtErrors = new DataTable();

            foreach (DataColumn dc in dt.Columns)
            {
                dtErrors.Columns.Add(dc.ColumnName);
                dtOk.Columns.Add(dc.ColumnName);
            }

            dtErrors.Columns.Add("Errors");

            BSCMFF.Core.RoleDataProvider.EntityRoleDataProvider rp = new Core.RoleDataProvider.EntityRoleDataProvider();

            BSCMFF.Core.BusinessLayer.DepartmentManager dm = new DepartmentManager();
            

            foreach (DataRow dr in dt.Rows)
            {
                bool hasError = false;
                string errors = "";

                object[] data = dr.ItemArray;
                //empty firstname
                
                if (data[0].ToString() == "")
                {
                    hasError=true;

                    if(errors == "")
                    {
                        errors = "Firstname is empty";
                    }
                    else
                    {
                        errors += ", Firstname is empty";
                    }
                }

                //empty lastname
                if (data[1].ToString() == "")
                {
                    hasError=true;

                    if(errors == "")
                    {
                        errors = "Lastname is empty";
                    }
                    else
                    {
                        errors += ", Lastname is empty";
                    }
                }

                //invalid email
                if (!IsValidEmailAddress(data[2].ToString()))
                {
                    hasError=true;

                    if(errors == "")
                    {
                        errors = "Invalid e-mail";
                    }
                    else
                    {
                        errors += ", Invalid e-mail";
                    }
                }

                
                //invalid role name
                string[] roles = data[3].ToString().Split(';');
                foreach (string r in roles)
                {
                    if (!(r== "" || rp.RoleExists(r)))
                    {
                        hasError = true;

                        if (errors == "")
                        {
                            errors = "Invalid Role name";
                        }
                        else
                        {
                            errors += ", Invalid Role name";
                        }
                        break;
                    }
                }


                //invalid department
                string depName = data[7].ToString();
                if (depName != "" && !dm.DepartmentExists(depName))
                {
                    hasError = true;

                    if (errors == "")
                    {
                        errors = "Invalid department name";
                    }
                    else
                    {
                        errors += ", Invalid department name";
                    }
                }

                //invalid hire date
                DateTime value = new DateTime();
                if (data[9].ToString() != "" && !DateTime.TryParse(data[9].ToString(), out value))
                {
                    hasError = true;

                    if (errors == "")
                    {
                        errors = "Invalid hire date";
                    }
                    else
                    {
                        errors += ", Invalid hire date";
                    }
                }

                //salary number
                if (data[10].ToString() != "")
                {
                    string salary = data[10].ToString().Trim();

                    double Num;

                    bool isNum = double.TryParse(salary, out Num);

                    if (!isNum)
                    {
                        hasError = true;

                        if (errors == "")
                        {
                            errors = "Salary isn't numeric";
                        }
                        else
                        {
                            errors += ", Salary isn't numeric";
                        }
                    }
                }

                if (hasError)
                {
                    string[] items = new string[dr.ItemArray.Length + 1]; //(string[])dr.ItemArray;

                    for (int i = 0; i < dr.ItemArray.Length; ++i)
                    {
                        items[i] = dr.ItemArray[i].ToString();
                    }

                    items[dr.ItemArray.Length] = errors;

                    dtErrors.Rows.Add(items);

                }
                else
                {
                    dtOk.Rows.Add(dr.ItemArray);
                }                
            }

            if (dtErrors.Rows.Count > 0)
            {
                
                RadGridErrors.DataSource = dtErrors;
                RadGridErrors.DataBind();
                PanelErrors.Visible = true;
                PanelPreview.Visible = false;

            }
            return dtOk;
        }

        /// <summary>
        /// Determines whether it is valid role name
        /// </summary>
        /// <param name="roleName">Name of the role.</param>
        /// <returns><c>true</c> if it is valid role name; otherwise, <c>false</c>.</returns>
        /// <remarks></remarks>
        private bool IsValidRoleName(string roleName)
        {
            if (roleName == "Administrator" || roleName == "Manager" || roleName == "Personalist" || roleName == "Employee")
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// Determines whether it is valid email address.
        /// </summary>
        /// <param name="sEmail">The s email.</param>
        /// <returns><c>true</c> if it is valid email address; otherwise, <c>false</c>.</returns>
        /// <remarks></remarks>
        public static bool IsValidEmailAddress(string sEmail)
        {
            if (sEmail == null)
            {
                return false;
            }
            else
            {
                return Regex.IsMatch(sEmail, @"
                    ^
                    [-a-zA-Z0-9][-.a-zA-Z0-9]*
                    @
                    [-.a-zA-Z0-9]+
                    (\.[-.a-zA-Z0-9]+)*
                    \.
                    (
                    com|edu|info|gov|int|mil|net|org|biz|
                    name|museum|coop|aero|pro|bsc
                    |
                    [a-zA-Z]{2}
                    )
                    $",
                RegexOptions.IgnorePatternWhitespace);
            }
        }

        /// <summary>
        /// Exports invalid user records into csv file.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        /// <remarks></remarks>
        protected void RadButtonExportCSV_Click(object sender, EventArgs e)
        {
            RadGridErrors.ExportSettings.OpenInNewWindow = true;
            foreach (GridColumn col in RadGridErrors.MasterTableView.RenderColumns)
            {
                col.Visible = true;
            }
            RadGridErrors.Rebind();
            RadGridErrors.MasterTableView.ExportToCSV();
        }

        /// <summary>
        /// Downloads import instructions with sample data.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Web.UI.ImageClickEventArgs"/> instance containing the event data.</param>
        /// <remarks></remarks>
        protected void ImageButtonZip_Click(object sender, ImageClickEventArgs e)
        {
            Response.Redirect("~/Resources/import instructions.zip");
        }
    }
}